﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PMQLCHH.DTO;
using System.Data;

namespace PMQLCHH.DAO
{
    public class KhachHangDAO
    {
        #region Select,Load

        public static DataTable LoadListKhachHang()
        {
            string sql = "Select kh.*,lkh.TenLoaiKhachHang from KHACHHANG kh,LOAIKHACHHANG lkh where kh.LoaiKhachHang=lkh.MaLoaiKhachHang";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable GetMaKhachHang()
        {
            string sql = "Select top 1 MaKhachHang from KHACHHANG order by MaKhachHang desc";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable LoadListLoaiKhachHang()
        {
            string sql = "Select MaLoaiKhachHang,TenLoaiKhachHang from LoaiKhachHang";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable SearchKhachHang(KhachHangDTO kh, int flag)
        {
            string sql = "";
            sql = "Select kh.*,lkh.TenLoaiKhachHang from KHACHHANG kh,LOAIKHACHHANG lkh where kh.LoaiKhachHang=lkh.MaLoaiKhachHang";
            if (flag == 0)
            {
                sql += " and kh.MaKhachHang like N'%" + kh.Makhachhang + "%'";
            }
            else if (flag == 1)
            {
                sql += " and kh.TenKhachHang like N'%" + kh.Tenkhachhang + "%'";
            }
            else if (flag == 2)
            {
                sql += " and kh.DiaChi like N'%" + kh.Diachi + "%'";
            }
            else if (flag == 3)
            {
                sql += " and kh.SoDienThoai like N'%" + kh.Sodienthoai + "%'";
            }
            if (kh.Loaikhachhang.ToString() != "0")
            {
                sql += " and kh.LoaiKhachHang =" + kh.Loaikhachhang + "";
            }
            
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

        //Kha Nguyen - ho tro cho Hoa Don - 12/6/2013 - begin
        /// <summary>
        /// Lay Thong Tin Khach Hang khi co ma khach hang
        /// </summary>
        ///
        public static DataTable LayThongTinKhachHang(string MaKH)
        {
            DataTable KH = new DataTable();
            string strQuery = String.Format("SELECT MaKhachHang, TenKhachHang,DiaChi, SoDienThoai,MaSoThue FROM KHACHHANG WHERE MaKhachHang = '{0}' ", MaKH);
            KH = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return KH;
        }
        //Kha Nguyen - ho tro cho Hoa Don - 12/6/2013 - end

        #endregion

        #region Insert,Delete,Update

        public static bool InsertKhachHang(KhachHangDTO kh)
        {
            bool kq;
            string sql = "";
            sql += String.Format("insert into KHACHHANG(MaKhachHang,TenKhachHang,DiaChi,SoDienThoai,Email,LoaiKhachHang,MaSoThue,GhiChu,TrangThai,Phai) values('{0}',N'{1}',N'{2}','{3}','{4}',{5},N'{6}',N'{7}',{8},{9})", kh.Makhachhang, kh.Tenkhachhang, kh.Diachi, kh.Sodienthoai, kh.Email, kh.Loaikhachhang, kh.Masothue, kh.Ghichu, kh.Trangthai, kh.Phai);
            kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }

        public static bool UpdateKhachHang(KhachHangDTO kh)
        {
            bool kq;
            string sql = "";
            sql += String.Format("Update KHACHHANG set TenKhachHang=N'" + kh.Tenkhachhang + "', DiaChi=N'" + kh.Diachi + "',SoDienThoai=N'" + kh.Sodienthoai + "',Email=N'" + kh.Email + "',LoaiKhachHang=" + kh.Loaikhachhang + ",MaSoThue=N'" + kh.Masothue + "',GhiChu='" + kh.Ghichu + "',Phai=" + kh.Phai + " where MaKhachHang='" + kh.Makhachhang + "'");
            kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }

        public static bool DeleteKhachHang(KhachHangDTO kh)
        {
            bool kq;
            string sql = "";
            sql += String.Format("Delete KHACHHANG where MaKhachHang='" + kh.Makhachhang + "'");
            kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }


        #endregion

    }
}
